package com.blog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.blog.BO.PostBO;
import com.blog.dao.AllPostDao;
import com.blog.dao.BaseDao;
import com.blog.entity.Post;

public class AllPostDaoImpl implements AllPostDao {

	@Override
	public List<Post> selectPost(PostBO postBO, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Post> postList = new ArrayList<Post>();
		List<Object>  params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT * FROM allpost where 1 = 1");
		if(postBO != null) {
			//查询条件
			if(postBO.getPostId() != 0) {
				sql.append(" AND postId = ?");
				params.add(postBO.getPostId());
			}//帖子id
			if(postBO.getPostTitle()!=null && !"".equals(postBO.getPostTitle())) {
				sql.append(" AND postTitle LIKE concat('%',?,'%')");
				params.add(postBO.getPostTitle());
			}//帖子标题模糊查询
			if(postBO.getPostText()!=null && !"".equals(postBO.getPostText())) {
				sql.append(" AND postText LIKE concat('%',?,'%')");
				params.add(postBO.getPostText());
			}//帖子内容模糊查询
			if(postBO.getPostUserId() != 0) {
				sql.append(" AND postUserId = ?");
				params.add(postBO.getPostUserId());
			}//帖子所属用户id
			if(postBO.getPostPlateId() != 0) {
				sql.append(" AND postPlateId = ?");
				params.add(postBO.getPostPlateId());
			}//帖子所属板块id
			if(postBO.getPostState() != 0) {
				sql.append(" AND postState = ?");
				params.add(postBO.getPostState());
			}//帖子状态
			if(postBO.getCreationTime()!=null) {
				sql.append(" AND creationTime = ?");
				params.add(postBO.getCreationTime());
			}//帖子创建时间
			if(postBO.getUpdateStartDate()!=null) {
				sql.append(" AND creationTime >= ?");
				params.add(postBO.getUpdateStartDate());
			}//帖子从什么时间开始，包含输入的时间
			if(postBO.getUpdateEndDate()!=null) {
				sql.append(" AND creationTime <= ?");
				params.add(postBO.getUpdateEndDate());
			}//帖子从什么时候结束，包含输入的时间
		}
		sql.append(" LIMIT ?,?");
		Post selectPost = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql.toString());
			for(int i =0;i<params.size();i++) {
				ps.setObject(i+1, params.get(i));
			}
			ps.setInt(params.size()+1, (pageNum-1)*pageSize);
			ps.setInt(params.size()+2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				selectPost = new Post();
				selectPost.setPostId(res.getInt("postId"));
				selectPost.setPostTitle(res.getString("postTitle"));
				selectPost.setPostText(res.getString("postText"));
				selectPost.setPostUserId(res.getInt("postUserId"));
				selectPost.setPostPlateId(res.getInt("postPlateId"));
				selectPost.setPostState(res.getInt("postState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("creationTime")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPost.setCreationTime(dt);
				postList.add(selectPost);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return postList;
	}

	@Override
	public int selectPostNum() {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT COUNT(1) AS postNum FROM allpost;";
		int postNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				postNum = res.getInt("postNum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return postNum;
	}

	@Override
	public Post selectPostByPostId(int postId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT * FROM allpost where postId = ?";
		Post selectPost = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,postId);
			res = ps.executeQuery();
			if(res.next()) {
				selectPost = new Post();
				selectPost.setPostId(res.getInt("postId"));
				selectPost.setPostTitle(res.getString("postTitle"));
				selectPost.setPostText(res.getString("postText"));
				selectPost.setPostUserId(res.getInt("postUserId"));
				selectPost.setPostId(res.getInt("postId"));
				selectPost.setPostState(res.getInt("postState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("creationTime")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPost.setCreationTime(dt);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return selectPost;
	}

	@Override
	public int insertPost(Post post) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO allpost(postTitle,postText,postUserId,postPlateId,postState,creationTime) VALUES (?,?,?,?,?,?);";
		int resultNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1,post.getPostTitle());
			ps.setString(2,post.getPostText());
			ps.setInt(3,post.getPostUserId());
			ps.setInt(4,post.getPostPlateId());
			ps.setInt(5,post.getPostState());
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String dt = format.format(post.getCreationTime().getTime());
			ps.setString(6,dt);
			resultNum = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, ps, con);
		}
		return resultNum;
	}

	@Override
	public int deletePostByPostId(int postId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "DELETE FROM allpost WHERE postId = ?;";
		int resultNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,postId);
			resultNum = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, ps, con);
		}
		return resultNum;
	}

	@Override
	public int updatePost(Post post) {
		// TODO Auto-generated method stub
		List<Object>  params = new ArrayList<Object>();
		int resultNum = 0;
		if(post.getPostId() != 0) {
			Connection con = null;
			PreparedStatement ps = null;
			StringBuffer sql = new StringBuffer("UPDATE allpost SET postId = ?");
			params.add(post.getPostId());
			if(post.getPostTitle()!=null && !"".equals(post.getPostTitle())) {
				sql.append(",postTitle = ?");
				params.add(post.getPostTitle());
			}//帖子标题
			if(post.getPostText()!=null && !"".equals(post.getPostText())) {
				sql.append(",postText = ?");
				params.add(post.getPostText());
			}//帖子内容
			if(post.getPostUserId() != 0) {
				sql.append(",postUserId = ?");
				params.add(post.getPostUserId());
			}//帖子所属用户id
			if(post.getPostPlateId() != 0) {
				sql.append(",postPlateId = ?");
				params.add(post.getPostPlateId());
			}//帖子所属板块id
			if(post.getPostState() != 0) {
				sql.append(",postState = ?");
				params.add(post.getPostState());
			}//帖子等级
			sql.append(",regDate = ?");
			sql.append(" WHERE postId = ?");
			try {
				con = BaseDao.getCon();
				ps = con.prepareStatement(sql.toString());
				for(int i =0;i<params.size();i++) {
					ps.setObject(i+1, params.get(i));
				}
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String dt = format.format(new Date().getTime());
				ps.setString(params.size()+1,dt);
				ps.setInt(params.size()+2,post.getPostId());
				resultNum = ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				BaseDao.closeAll(null, ps, con);
			}
		}
		return resultNum;
	}

	@Override
	public List<Post> selectAllPostByPostUserId(int postUserId, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Post> postList = new ArrayList<Post>();
		String sql = "SELECT * FROM allpost where postUserId = ? LIMIT ?,?";
		Post selectPost = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,postUserId);
			ps.setInt(2, (pageNum-1)*pageSize);
			ps.setInt(3, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				selectPost = new Post();
				selectPost.setPostId(res.getInt("postId"));
				selectPost.setPostTitle(res.getString("postTitle"));
				selectPost.setPostText(res.getString("postText"));
				selectPost.setPostUserId(res.getInt("postUserId"));
				selectPost.setPostId(res.getInt("postId"));
				selectPost.setPostState(res.getInt("postState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("creationTime")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPost.setCreationTime(dt);
				postList.add(selectPost);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return postList;
	}

}
